import prisma from '../lib/prisma';
import { logger } from '../utils/logger';

export class StatisticsService {
  // 获取全校成绩统计
  static async getSchoolStatistics(schoolId: string) {
    try {
      // 获取所有评估的成绩统计
      const evaluationStats = await prisma.submission.groupBy({
        by: ['evaluationId'],
        _avg: {
          score: true
        },
        _max: {
          score: true
        },
        _min: {
          score: true
        }
      });

      // 获取不同分数段的学生分布
      const scoreRangeDistribution = await prisma.$queryRaw`
        SELECT
          CASE
            WHEN score >= 90 THEN '优秀'
            WHEN score >= 80 THEN '良好'
            WHEN score >= 70 THEN '中等'
            WHEN score >= 60 THEN '及格'
            ELSE '不及格'
          END as range,
          COUNT(*) as count
        FROM "Submission"
        GROUP BY range
        ORDER BY MIN(score) DESC
      `;

      // 获取各学科的平均分
      const subjectAverages = await prisma.submission.groupBy({
        by: ['questionId'],
        _avg: {
          score: true
        }
      });

      // 获取总体及格率
      const totalCount = await prisma.submission.count();
      const passCount = await prisma.submission.count({
        where: {
          score: {
            gte: 60
          }
        }
      });
      const passRate = (passCount / totalCount) * 100;

      // 获取最近趋势数据
      const trendData = await prisma.submission.groupBy({
        by: ['evaluationId'],
        _avg: {
          score: true
        },
        orderBy: {
          evaluationId: 'desc'
        },
        take: 10
      });

      return {
        evaluationStats,
        scoreRangeDistribution,
        subjectAverages,
        passRate,
        trendData,
        totalSubmissions: totalCount
      };
    } catch (error) {
      logger.error('Error getting school statistics', { error });
      throw error;
    }
  }

  // 获取班级成绩对比
  static async getClassComparison(schoolId: string) {
    try {
      // 获取各班级的平均分
      const classAverages = await prisma.submission.groupBy({
        by: ['userId'],
        _avg: {
          score: true
        }
      });

      // 获取班级排名
      const classRankings = await prisma.$queryRaw`
        WITH class_scores AS (
          SELECT
            c.id as class_id,
            c.name as class_name,
            AVG(s.score) as average_score,
            COUNT(DISTINCT s.userId) as student_count
          FROM "Class" c
          JOIN "Student" st ON st."classId" = c.id
          JOIN "Submission" s ON s."userId" = st.id
          GROUP BY c.id, c.name
        )
        SELECT
          class_id,
          class_name,
          average_score,
          student_count,
          RANK() OVER (ORDER BY average_score DESC) as rank
        FROM class_scores
        ORDER BY rank
      `;

      // 获取各班级的分数分布
      const classDistributions = await prisma.$queryRaw`
        WITH score_ranges AS (
          SELECT
            c.id as class_id,
            c.name as class_name,
            CASE
              WHEN s.score >= 90 THEN '优秀'
              WHEN s.score >= 80 THEN '良好'
              WHEN s.score >= 70 THEN '中等'
              WHEN s.score >= 60 THEN '及格'
              ELSE '不及格'
            END as range,
            COUNT(*) as count
          FROM "Class" c
          JOIN "Student" st ON st."classId" = c.id
          JOIN "Submission" s ON s."userId" = st.id
          GROUP BY c.id, c.name, range
        )
        SELECT *
        FROM score_ranges
        ORDER BY class_id, range
      `;

      return {
        classAverages,
        classRankings,
        classDistributions
      };
    } catch (error) {
      logger.error('Error getting class comparison', { error });
      throw error;
    }
  }

  // 获取年级成绩对比
  static async getGradeComparison(schoolId: string) {
    try {
      // 获取各年级的平均分
      const gradeAverages = await prisma.$queryRaw`
        SELECT
          c.grade,
          AVG(s.score) as average_score,
          COUNT(DISTINCT s.userId) as student_count
        FROM "Class" c
        JOIN "Student" st ON st."classId" = c.id
        JOIN "Submission" s ON s."userId" = st.id
        GROUP BY c.grade
        ORDER BY c.grade
      `;

      // 获取各年级的及格率
      const gradePassRates = await prisma.$queryRaw`
        WITH grade_stats AS (
          SELECT
            c.grade,
            COUNT(*) as total_count,
            COUNT(CASE WHEN s.score >= 60 THEN 1 END) as pass_count
          FROM "Class" c
          JOIN "Student" st ON st."classId" = c.id
          JOIN "Submission" s ON s."userId" = st.id
          GROUP BY c.grade
        )
        SELECT
          grade,
          total_count,
          pass_count,
          ROUND((pass_count::FLOAT / total_count) * 100, 2) as pass_rate
        FROM grade_stats
        ORDER BY grade
      `;

      return {
        gradeAverages,
        gradePassRates
      };
    } catch (error) {
      logger.error('Error getting grade comparison', { error });
      throw error;
    }
  }
}
